OpenEdge Development:<br />Progress 4GL Handbook Using temporary tables in your application

Temporary tables, or temp-tables, are effectively database tables that Progress stores in a temporary database. You use them when you need temporary storage for multiple rows of data during a session, and when you need to pass data between OpenEdge sessions on different machines, such as between a server and a client using the OpenEdge AppServer™. Temp-tables exist only for the duration of the procedure that defines them or, at most, for the duration of a OpenEdge session.

A temp-table is private, visible only to the session that creates it or receives it as a parameter passed from another session. But because temp-tables use the same support code that actual database tables use, you can take advantage of almost all database features that don’t require persistence of data and multi-user access to data, for example defining indexes for fields in the temp-table.

Temp-tables are a wonderfully useful construct in the Progress 4GL, and you can apply them for a many different kinds of programming problems. Fundamentally, you can think of them as providing two basic capabilities:

Temp-tables allow you to define a table within a session that does not map to any single database table. A temp-table can be based on a database table, but you can then add fields that represent calculations or fields from other tables or any other type of data source. Or you can define a temp-table that is not related in any way to any database table, that is used for example to store calculations requiring a two-dimensional table of data that is then used in a report or in some other way.

Temp-tables allow you to pass data between OpenEdge sessions. When you begin to build distributed applications, which you can do with your own AppServer statements in the 4GL, you allow your application to be divided between procedures that are on the same machine with your application database and procedures that execute on a client machine that produces the user interface and interacts with the user. You use the OpenEdge AppServer product to communicate between client OpenEdge sessions and server sessions. When a client procedure needs to get data from the server, it runs a 4GL procedure on the server that returns data as an OUTPUT parameter, or that accepts updates from the client as an INPUT parameter. You define these parameters as temp-tables that you pass between the sessions. You cannot pass record buffers as parameters between sessions, so whether you need to pass a single row or many rows together, you do this in the form of a temp-table as a parameter.

Because the client session is sending and receiving only temp-tables and not dealing directly with database records, your client sessions can run without a database connection of any kind, which makes it easier to build flexible and efficient distributed applications. Temp-tables are therefore a fundamental part of any distributed application. Writing your own 4GL statements to use the OpenEdge AppServer is beyond the scope of this book, but in this chapter you’ll write a procedure that passes a temp-table as a parameter to a user interface program, so that you can begin to get a feel for what is means to design your procedures for this kind of separation of user interface from database access and business logic.